<?php

namespace app\api\controller;

use app\admin\model\Admin;
use app\admin\model\Employ;
use app\common\controller\Api;
use app\common\library\Auth;
use app\common\model\Department;
use app\common\model\Equipment;
use app\common\model\Fault;
use app\common\model\Hospital;
use app\common\model\News;
use app\common\model\work\Service;
use think\Db;
use think\Exception;

/**
 * 二期接口
 */
class Phase extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

     /**
     * 定时任务--修改维保工单状态
     * */
    public function maintenance(){
        $list=\app\common\model\work\Warranty::where(["type_data"=>'1'])->where("status != '3'")->select();
        if(!empty($list)){
            foreach ($list as $v){
               $service=Service::whereTime("servicetime",">=",time())->where(["warranty_id"=>$v["id"]])->find();
               if(empty($service)){
                   //没有大于当前时间的工单
                   \app\common\model\work\Warranty::where(["id"=>$v["id"]])->update([
                       "status"=>'3',
                   ]);
               }
            }
        }
    }

    /**
     * 获取设备信息
     * */
    public function getEquipment(){
        if (!$this->request->post("id")) $this->error("id为空");
        $id=$this->request->post("id");
        $list=Equipment::with(["department"=>function($query){
            $query->withField('id,name,hospital_id');
        }])->field("equipment.*,a.name hospital_name")
            ->join("hospital a","department.hospital_id=a.id")
            ->where(["equipment.id"=>$id])
        ->find();
        if($list){
            $next_json=json_decode($list["next_json"],true);
            if($next_json){
                $next=$this->find_closest($next_json, date("Y-m-d",time()));
                $list["next"]=$next?$next:$next_json[count($next_json)-1];
            }
        }
        $this->success("成功",$list);
    }
    function find_closest($array, $date)
    {
        foreach($array as $k=>$day)
        {
            $interval[] = abs(strtotime($date) - strtotime($day["time"]));
        }
        asort($interval);
        $closest = key($interval);
        return $array[$closest]["time"];

    }
    /**
     *科室列表
     * */
    public function getDepartment(){
        $user=$this->auth->getUser();
        //$user["id"]=16;
        $user["identity"]=1;
        if(!empty($user["identity"])){
            if (!$this->request->post("hospital_id")) $this->error("医院为空");
            $hospital_id=$this->request->post("hospital_id");
        }else{
            $hospital=Hospital::where(["status"=>1])->where('FIND_IN_SET("'.$user["id"].'", user_ids)')->find();
            if(empty($hospital)) $this->error('该用户不是医院账号' );
            $hospital_id=$hospital["id"];
        }
        $name=$this->request->param("name")?$this->request->param("name"):'';
        $list=Department::field("id,name")->where(["hospital_id"=>$hospital_id])
            ->whereLike("name","%".$name."%")
            ->select();
        $this->success('成功',$list);

    }
    /**
     *设备列表
     * */
    public function getEquipmentList(){
        if (!$this->request->post("department_id")) $this->error("科室id为空");
        $name=$this->request->param("name")?$this->request->param("name"):'';
        $list=Equipment::field("id,name")->where(["department_id"=>$this->request->post("department_id")])
            ->whereLike("name","%".$name."%")
            ->select();
        $this->success('成功',$list);
    }
    /**
     *设备列表
     * */
    public function getEquipmentList2(){
        if (!$this->request->post("department_id")) $this->error("科室id为空");
        if (!$this->request->post("name")) $this->error("设备为空");
        $name=$this->request->param("name")?$this->request->param("name"):'';
        $eq=Equipment::field("name")->where(["id"=>$name])->find();
        if(empty($eq)) $this->error("设备不存在");
        $list=Equipment::field("id,serial name")->where(["department_id"=>$this->request->post("department_id")])
            ->where(["name"=>$eq["name"]])
            ->whereLike("serial","%".$this->request->param("serial")."%")
            ->select();
        $this->success('成功',$list);
    }
    /**
     * 提报故障
     * */
    public function fault(){
        if (!$this->request->post("equipment_id")) $this->error("设备id为空");
        $id=$this->request->post("equipment_id");
        $list=Equipment::get(["id"=>$id]);
        if(empty($list)) $this->error("设备不存在");
        $data=[
            ['problem', 'require', '问题描述不能为空'],
            ['contacts', 'require', '联系人不能为空'],
            ['contacts_mobile', 'require', '联系电话不能为空'],
        ];
        $validate = new \think\Validate($data);
        if (!$validate->check(input())) {
            $this->error('提交失败：' . $validate->getError());
        }
        $param = $this->request->param();
        $param["createtime"]=time();
        Db::startTrans();
        try {
            $mode=new Fault();
            $mode->allowField(true)->save($param);
            $list->save(["status"=>'0']);
            $department=Department::with(["user"=>function($query){
                $query->withField('id,mobile');
            }])->field("a.name hospital_name,a.id hospital_id")
                ->join("hospital a","department.hospital_id=a.id")
                ->where(["department.id"=>$list["department_id"]])->find();
            if($department["user"]["mobile"]){
                $str=[
                    "hospital_name"=>$department["hospital_name"],
                    "department_name"=>$department["name"],
                    "equipment_name"=>$list["name"],
                    "phone"=>$param["contacts_mobile"],
                ];
                $sms=new Sms();
                $ret = $sms->hairsendSms($department["user"]["mobile"], $str);
            }
            $time=time();
            News::insertAll([[
                "hospital_id"=>$department["hospital_id"],
                "news"=>"故障单消息已发送短信至相应驻场工程师。",
                "type_data"=>"1",
                "status"=>"0",
                "createtime"=>$time,
            ],
                [
                    "hospital_id"=>$department["hospital_id"],
                    "news"=>"您有一个故障单，请注意跟进。",
                    "type_data"=>"1",
                    "status"=>"0",
                    "createtime"=>$time,
                ]
            ]);
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        $this->success('成功');
    }
    /**
     * 使用
     * */
    public function employ(){
        if (!$this->request->post("equipment_id")) $this->error("设备id为空");
        $employ=Employ::get(["equipment_id"=>$this->request->post("equipment_id")]);
        //if($employ) $this->error("已使用过");
        Employ::create([
            "equipment_id"=>$this->request->post("equipment_id"),
            "createtime"=>time(),
        ]);
        $this->success('成功');

    }
    /**
     *大屏数据
     * */
    public function getRecord(){
        $where1="1=1";
        $Ids=$this->auth->getGroupIds();
        $user=$this->auth->getUserInfo();
        $department=Department::field("concat(',',id)")->where($where1)->select();
        dump($department);die();
        if($Ids[0]==2) {
            $admin = Admin::get($this->user["id"]);
            if ($admin) {
                //$where1 = ["account" => $admin["username"]];
                $hospital = Hospital::field("id")->where(["status" => 1])->where('FIND_IN_SET("' . $this->user["id"] . '", admin_ids)')->find();
                $where1=["hospital_id"=>$hospital["id"]];
                $list["ok_num"]=Fault::with("equipment")->where($where1)->where(["fault.status"=>'1'])->count();//已完成的故障数
                $list["all_num"]=Fault::with("equipment")->where($where1)->count();//总的故障数
                $list["equipment_num"]=Equipment::with("equipment")->where($where1)->count();//总的设备数
                $sql="SELECT COUNT(id) num FROM yl_equipment WHERE id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time < DATE_FORMAT( NOW(),'%Y-%m-%d'))";
                $equipment_ok_num=Db::query($sql);//已保养设备数
                $sql="SELECT COUNT(id) num FROM yl_equipment WHERE id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d'))";
                $maintained_num=Db::query($sql);//待保养设备数
                $list["maintained_num"]=$maintained_num?$maintained_num[0]["num"]:0;
                $list["equipment_ok_num"]=$equipment_ok_num?$equipment_ok_num[0]["num"]:0;//已保养设备数
            }
        }else{
            $list["ok_num"]=Fault::where(["status"=>'1'])->count();//已完成的故障数
            $list["all_num"]=Fault::count();//总的故障数
            $list["equipment_num"]=Equipment::count();//总的设备数
            $sql="SELECT COUNT(id) num FROM yl_equipment WHERE id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time < DATE_FORMAT( NOW(),'%Y-%m-%d'))";
            $equipment_ok_num=Db::query($sql);//已保养设备数
            $sql="SELECT COUNT(id) num FROM yl_equipment WHERE id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d'))";
            $maintained_num=Db::query($sql);//待保养设备数
            $list["maintained_num"]=$maintained_num?$maintained_num[0]["num"]:0;
            $list["equipment_ok_num"]=$equipment_ok_num?$equipment_ok_num[0]["num"]:0;//已保养设备数
        }
        $this->success('成功',$list);
    }
    /**
     *故障率
     * */
    public function failure(){
        $list["equipment_num"]=Equipment::count();//总的设备数
        $failure=Fault::count();//故障数
        $month=Fault::whereTime('createtime','month')->count();//查询本月故障数;
        $week=Fault::whereTime('createtime','week')->count();//查询本周故障数;
        $list["equipment_ure_num"]=sprintf("%.2f", $failure/$list["equipment_num"]);;//设备故障率
        $list["month"]=sprintf("%.2f", $month/$list["equipment_num"]);;//设备本月故障率
        $list["week"]=sprintf("%.2f", $week/$list["equipment_num"]);;//设备本周故障率
        $this->success('成功',$list);
    }
    /**
     * 设备故障情况
     * */
    public function faultLog(){
        $type=!$this->request->post("type")?0:$this->request->post("type");
        //重点是FROM_UNIXTIME的用法（%u是以周一为一周的开始，%U是以周日为一周的开始）

        if($type==1){
            //按周
            $sql="SELECT d.date, IFNULL(T.num,0) num FROM 
(
    SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) AS DATE
)  d
LEFT JOIN(
    SELECT FROM_UNIXTIME(createtime,'%Y-%m-%d') createtime, COUNT(id) AS num
    FROM yl_fault
    GROUP BY FROM_UNIXTIME(createtime,'%Y-%m-%d')
) T ON T.createtime = d.date
GROUP BY d.date";
        }else{
            $sql = "SELECT COUNT(id)total,'第一周' AS data_date FROM yl_fault h
WHERE  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1) AND h.createtime >= DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY)
UNION
SELECT COUNT(id)total,'第二周' AS data_date FROM yl_fault h
WHERE  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+1 
UNION
SELECT COUNT(id)total,'第三周' AS data_date FROM yl_fault h
WHERE    WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+2 
UNION
SELECT COUNT(id)total,'第四周' AS data_date FROM yl_fault h
WHERE   WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+3 
UNION
SELECT COUNT(id)total,'第五周' AS data_date FROM yl_fault h
WHERE WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+4 AND h.createtime >= LAST_DAY(CURDATE())
";
        }
        $row = Db::query($sql);
        $this->success('成功',$row);
    }
    /**
     * 待保养设备 待修改
     * */
    public function maintained(){
        $type=!$this->request->post("type")?0:$this->request->post("type");
        if($type==1){
            //按月
          /* $sql='SELECT SUM( CASE MONTH(FROM_UNIXTIME(createtime)) WHEN    "1" THEN (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=1 and status="0") ELSE 0 END) AS "一月",
 SUM(CASE MONTH(FROM_UNIXTIME(createtime)) WHEN "2" THEN  (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=2 and status="0") ELSE  0 END ) AS "二月",
 SUM(CASE MONTH(FROM_UNIXTIME(createtime)) WHEN  "3" THEN (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=3 and status="0") ELSE 0 END) AS "三月",
 SUM( CASE MONTH(FROM_UNIXTIME(createtime)) WHEN "4" THEN (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=4 and status="0") ELSE 0  END) AS "四月",
 SUM(CASE MONTH(FROM_UNIXTIME(createtime)) WHEN  "5" THEN (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=5 and status="0") ELSE 0 END) AS "五月",
 SUM( CASE MONTH(FROM_UNIXTIME(createtime))  WHEN "6" THEN  (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=6 and status="0") ELSE  0 END) AS "六月",
 SUM( CASE    MONTH(FROM_UNIXTIME(createtime))  WHEN   "7" THEN  (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=7 and status="0")  ELSE  0  END) AS "七月",
 SUM( CASE    MONTH(FROM_UNIXTIME(createtime))  WHEN  "8" THEN   (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=8 and status="0")  ELSE   0  END ) AS "八月",
 SUM( CASE    MONTH(FROM_UNIXTIME(createtime))  WHEN  "9" THEN  (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=9 and status="0") ELSE  0 END ) AS "九月",
 SUM( CASE    MONTH(FROM_UNIXTIME(createtime))  WHEN  "10" THEN   (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=10 and status="0")  ELSE   0  END ) AS "十月",
 SUM( CASE    MONTH(FROM_UNIXTIME(createtime))  WHEN  "11" THEN  (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=11 and status="0")  ELSE   0  END ) AS "十一月",
 SUM( CASE    MONTH(FROM_UNIXTIME(createtime)) WHEN  "12" THEN  (SELECT COUNT(id) FROM yl_fault WHERE MONTH(FROM_UNIXTIME(`createtime`))=12 and status="0") ELSE 0  END ) AS "十二月" FROM yl_fault
 ';*/
         /* $sql="SELECT d.date, IFNULL(T.num,0) num FROM
(
    SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) AS DATE
)  d
LEFT JOIN(
    SELECT FROM_UNIXTIME(createtime,'%Y-%m-%d') createtime, COUNT(id) AS num
    FROM yl_fault where status ='0'
    GROUP BY FROM_UNIXTIME(createtime,'%Y-%m-%d')
) T ON T.createtime = d.date
GROUP BY d.date";*/
            $sql="SELECT d.date, IFNULL(T.num,0) num FROM 
(
    SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) AS DATE
)  d
LEFT JOIN(
    SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime
) T ON T.createtime = d.date
ORDER BY d.date";
        }else{

            //重点是FROM_UNIXTIME的用法（%u是以周一为一周的开始，%U是以周日为一周的开始）
            /* $sql = "SELECT COUNT(id)total,'第一周' AS data_date FROM yl_fault h
 WHERE  status='0'  and  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1) AND h.createtime >= DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY)
 UNION
 SELECT COUNT(id)total,'第二周' AS data_date FROM yl_fault h
 WHERE  status='0'  and  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+1
 UNION
 SELECT COUNT(id)total,'第三周' AS data_date FROM yl_fault h
 WHERE  status='0'  and    WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+2
 UNION
 SELECT COUNT(id)total,'第四周' AS data_date FROM yl_fault h
 WHERE   status='0'  and  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+3
 UNION
 SELECT COUNT(id)total,'第五周' AS data_date FROM yl_fault h
 WHERE  status='0' and  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+4 AND h.createtime >= LAST_DAY(CURDATE())
 ";*/
            $sql = "SELECT COUNT(1)total,'第一周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE    WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1) 
AND h.createtime >= DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY)
UNION
SELECT COUNT(1)total,'第二周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE   WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+1 
UNION
SELECT COUNT(1)total,'第三周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE  WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+2 
UNION
SELECT COUNT(1)total,'第四周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE   WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+3 
UNION
SELECT COUNT(1)total,'第五周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE  WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+4 AND h.createtime >= LAST_DAY(CURDATE())
";
           /*$sql="SELECT COUNT(1)total,'第一周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE    WEEK(h.createtime, 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1) 
AND h.createtime >= DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY)
UNION
SELECT COUNT(1)total,'第二周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE   WEEK(h.createtime, 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+1 
UNION
SELECT COUNT(1)total,'第三周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE  WEEK(h.createtime, 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+2 
UNION
SELECT COUNT(1)total,'第四周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE   WEEK(h.createtime, 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+3 
UNION
SELECT COUNT(1)total,'第五周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')) a
 GROUP BY a.createtime) h
WHERE  WEEK(h.createtime, 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+4 AND h.createtime >= LAST_DAY(CURDATE())
";*/
        }
        $row["data"] = Db::query($sql);
        $sql="SELECT a.id,a.name,marking,department_id,q.name department_name  FROM yl_equipment a
JOIN yl_department q ON q.id=a.department_id
 WHERE a.id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d'))
";
        $row["all"] = Db::query($sql);//待保养设备
        $this->success('成功',$row);
    }
    /**
     *故障设备
     * */
    public function faultList(){
        $list=Fault::with(["equipment"=>function($query){
            $query->withField('id,name,marking,department_id');
        }])->field("a.id,a.name")
        ->join("department a","a.id=equipment.department_id")
        ->select();
        $this->success('成功',$list);
    }
    /**设备操作记录
     *
     * */
    public function operationLog(){
        $type=!$this->request->post("type")?0:$this->request->post("type");
        $list["data"]=Employ::field("equipment_id,b.name,count(1) num")
            ->join("equipment a","yl_employ.equipment_id=a.id")
            ->join("department b","b.id=a.department_id")
            ->whereTime('yl_employ.createtime','week')
            ->group("equipment_id")->order("num")->limit(5)->select();//本周
        if($type==1){
            $list["data"]=Employ::field("equipment_id,b.name,count(1) num")
                ->join("equipment a","yl_employ.equipment_id=a.id")
                ->join("department b","b.id=a.department_id")
                ->whereTime('yl_employ.createtime','month')
                ->group("equipment_id")->order("num")->limit(5)->select();//本月
        }
        $list["all"]=Employ::field("*,FROM_UNIXTIME(employ.createtime, '%Y-%m-%d %H:%i:%S') createtime,b.name department_name")
        ->with(["equipment"=>function($query){
            $query->withField('id,name,marking,department_id');
        }])
            ->join("department b","b.id=equipment.department_id")->select();
        $this->success('成功',$list);
    }

}
